﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public class D_FIM_MAS_BankAccounts : IDAL_FIM_MAS_BankAccounts
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, ConfigurationSettings.AppSettings["ConnectionString"].ToString());
        ConvertNullable cn = new ConvertNullable();

        public IList<M_FIM_MAS_BankAccounts> GetFull()
        {
            return GetFull(null);
        }

        public IList<M_FIM_MAS_BankAccounts> GetFull(Nullable<int> bank_acc_type_grp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MAS_BankAccounts> infoList = new List<M_FIM_MAS_BankAccounts>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bank_acc_type_grp_seq", bank_acc_type_grp_seq);

                strSQL = "SELECT acc.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name, acc.bank_acc_name_eng"
                    + ", acc.bank_acc_type_grp_seq, acc.branch_seq, acc.bgm_source_seq"//, acc.sub_source_seq"
                    + ", acc.saving_bank_acc_seq, acc.account_seq, acc.work_area_seq, acc.remarks"
                    + ", at.bank_acc_type_grp_name"
                    + ", case when acc.path = '1' then 'เงินทดรองราชการ' else s.bgm_source_name end as bgm_source_name"//, sub.sub_source_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", sav.bank_acc_no as saving_bank_acc_no, sav.bank_acc_name as saving_bank_acc_name"
                    + ", area.work_area_name, gl.account_name"
                    + ", acc.record_status, coalesce(acc.path, '0') as path"
                    + ", acc.created_by, acc.created_when, acc.updated_by, acc.updated_when"
                    + " FROM fim_mas_bank_accounts acc"
                    + " LEFT JOIN fim_mas_bank_account_type_groups at ON acc.bank_acc_type_grp_seq = at.bank_acc_type_grp_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN bgm_mas_source s ON acc.bgm_source_seq = s.bgm_source_seq"
                    //+ " LEFT JOIN fim_mas_sub_sources sub ON acc.sub_source_seq = sub.sub_source_seq"
                    + " LEFT JOIN fim_mas_bank_accounts sav ON acc.saving_bank_acc_seq = sav.bank_acc_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON acc.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN acm_mas_account gl ON acc.account_seq = gl.account_seq"
                    + " WHERE acc.record_status not like @delete_status"
                    + (bank_acc_type_grp_seq != null ? " and acc.bank_acc_type_grp_seq = @bank_acc_type_grp_seq" : "")
                    + " ORDER BY acc.bank_acc_no";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_BankAccounts info = new M_FIM_MAS_BankAccounts();
                        info.bank_acc_seq = Convert.ToInt32(drc[i]["bank_acc_seq"]);
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.bank_acc_name_eng = drc[i]["bank_acc_name_eng"].ToString();
                        info.bank_acc_type_grp_seq = cn.ConvertNullInt(drc[i]["bank_acc_type_grp_seq"]);
                        info.branch_seq = Convert.ToInt32(drc[i]["branch_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        //info.sub_source_seq = cn.ConvertNullInt(drc[i]["sub_source_seq"]);
                        info.saving_bank_acc_seq = cn.ConvertNullInt(drc[i]["saving_bank_acc_seq"]);
                        info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        info.bank_acc_type_grp_name = drc[i]["bank_acc_type_grp_name"].ToString();
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        //info.sub_source_name = drc[i]["sub_source_name"].ToString();
                        info.bank_seq = cn.ConvertNullInt(drc[i]["bank_seq"]);
                        info.bank_id = drc[i]["bank_id"].ToString();
                        info.bank_name = drc[i]["bank_name"].ToString();
                        info.branch_id = drc[i]["branch_id"].ToString();
                        info.branch_name = drc[i]["branch_name"].ToString();
                        info.saving_bank_acc_no = drc[i]["saving_bank_acc_no"].ToString();
                        info.saving_bank_acc_name = drc[i]["saving_bank_acc_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.account_name = drc[i]["account_name"].ToString();
                        info.path = drc[i]["path"].ToString();
                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MAS_BankAccounts> GetFull(Nullable<int> bank_acc_type_grp_seq
            , Nullable<int> bgm_source_seq)//, Nullable<int> sub_source_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MAS_BankAccounts> infoList = new List<M_FIM_MAS_BankAccounts>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bank_acc_type_grp_seq", bank_acc_type_grp_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                //dbManager.AddParameters(3, "@sub_source_seq", sub_source_seq);

                strSQL = "SELECT acc.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name, acc.bank_acc_name_eng"
                    + ", acc.bank_acc_type_grp_seq, acc.branch_seq, acc.bgm_source_seq"//, acc.sub_source_seq"
                    + ", acc.saving_bank_acc_seq, acc.account_seq, acc.work_area_seq, acc.remarks"
                    + ", at.bank_acc_type_grp_name"
                    + ", case when acc.path = '1' then 'เงินทดรองราชการ' else s.bgm_source_name end as bgm_source_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", save.bank_acc_no as saving_bank_acc_no, save.bank_acc_name as saving_bank_acc_name"
                    + ", area.work_area_name, gl.account_name, coalesce(acc.path, '0') as path"
                    + ", acc.record_status"
                    + ", acc.created_by, acc.created_when, acc.updated_by, acc.updated_when"
                    + " FROM fim_mas_bank_accounts acc"
                    + " LEFT JOIN fim_mas_bank_account_type_groups at ON acc.bank_acc_type_grp_seq = at.bank_acc_type_grp_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN bgm_mas_source s ON acc.bgm_source_seq = s.bgm_source_seq"
                    //+ " LEFT JOIN fim_mas_sub_sources sub ON acc.sub_source_seq = sub.sub_source_seq"
                    + " LEFT JOIN fim_mas_bank_accounts save ON acc.saving_bank_acc_seq = save.bank_acc_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON acc.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN acm_mas_account gl ON acc.account_seq = gl.account_seq"
                    + " WHERE acc.record_status not like @delete_status"
                    + (bank_acc_type_grp_seq != null ? " and acc.bank_acc_type_grp_seq = @bank_acc_type_grp_seq" : "")
                    + (bgm_source_seq != null ? " and acc.bgm_source_seq = @bgm_source_seq" : "")
                    //+ (sub_source_seq != null ? " and acc.sub_source_seq = @sub_source_seq" : "")
                    + " ORDER BY acc.bank_acc_no";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_BankAccounts info = new M_FIM_MAS_BankAccounts();
                        info.bank_acc_seq = Convert.ToInt32(drc[i]["bank_acc_seq"]);
                        info.bank_acc_no = drc[i]["bank_acc_no"].ToString();
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        info.bank_acc_name_eng = drc[i]["bank_acc_name_eng"].ToString();
                        info.bank_acc_type_grp_seq = cn.ConvertNullInt(drc[i]["bank_acc_type_grp_seq"]);
                        info.branch_seq = Convert.ToInt32(drc[i]["branch_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        //info.sub_source_seq = cn.ConvertNullInt(drc[i]["sub_source_seq"]);
                        info.saving_bank_acc_seq = cn.ConvertNullInt(drc[i]["saving_bank_acc_seq"]);
                        info.account_seq = cn.ConvertNullInt(drc[i]["account_seq"]);
                        info.work_area_seq = cn.ConvertNullInt(drc[i]["work_area_seq"]);
                        info.remarks = drc[i]["remarks"].ToString();
                        info.bank_acc_type_grp_name = drc[i]["bank_acc_type_grp_name"].ToString();
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        //info.sub_source_name = drc[i]["sub_source_name"].ToString();
                        info.bank_seq = cn.ConvertNullInt(drc[i]["bank_seq"]);
                        info.bank_id = drc[i]["bank_id"].ToString();
                        info.bank_name = drc[i]["bank_name"].ToString();
                        info.branch_id = drc[i]["branch_id"].ToString();
                        info.branch_name = drc[i]["branch_name"].ToString();
                        info.saving_bank_acc_no = drc[i]["saving_bank_acc_no"].ToString();
                        info.saving_bank_acc_name = drc[i]["saving_bank_acc_name"].ToString();
                        info.work_area_name = drc[i]["work_area_name"].ToString();
                        info.account_name = drc[i]["account_name"].ToString();
                        info.path = drc[i]["path"].ToString();

                        info.record_status = drc[i]["record_status"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_MAS_BankAccounts> GetList(Nullable<int> bank_acc_type_grp_seq
            , Nullable<int> bgm_source_seq, int? bank_seq, int? branch_seq, bool bAdvance = false)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_MAS_BankAccounts> infoList = new List<M_FIM_MAS_BankAccounts>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bank_acc_type_grp_seq", bank_acc_type_grp_seq);
                dbManager.AddParameters(2, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(3, "@bank_seq", bank_seq);
                dbManager.AddParameters(4, "@branch_seq", branch_seq);

                strSQL = strSQL = "SELECT acc.bank_acc_seq, concat(acc.bank_acc_no, ' : ', acc.bank_acc_name, '  ', b.bank_name) as bank_acc_name"
                    + " FROM fim_mas_bank_accounts acc"
                    + " LEFT JOIN fim_mas_bank_account_type_groups at ON acc.bank_acc_type_grp_seq = at.bank_acc_type_grp_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN bgm_mas_source s ON acc.bgm_source_seq = s.bgm_source_seq"
                    + " WHERE acc.record_status not like @delete_status"
                    + (bank_acc_type_grp_seq != null ? " and acc.bank_acc_type_grp_seq = @bank_acc_type_grp_seq" : "")
                    + (!bAdvance ? (bgm_source_seq != null ? " and acc.bgm_source_seq = @bgm_source_seq and coalesce(acc.path, '0') = '0'" : "") : " and coalesce(acc.path, '0') = '1'")
                    + (bank_seq != null ? " and bb.bank_seq = @bank_seq" : "")
                    + (branch_seq != null ? " and acc.branch_seq = @branch_seq" : "")
                    + " ORDER BY bank_acc_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_MAS_BankAccounts info = new M_FIM_MAS_BankAccounts();
                        info.bank_acc_seq = Convert.ToInt32(drc[i]["bank_acc_seq"]);
                        info.bank_acc_name = drc[i]["bank_acc_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_MAS_BankAccounts GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_MAS_BankAccounts info = new M_FIM_MAS_BankAccounts();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT acc.bank_acc_seq, acc.bank_acc_no, acc.bank_acc_name, acc.bank_acc_name_eng"
                    + ", acc.bank_acc_type_grp_seq, acc.branch_seq, acc.bgm_source_seq"//, acc.sub_source_seq"
                    + ", acc.saving_bank_acc_seq, acc.account_seq, acc.work_area_seq, acc.remarks"
                    + ", tg.bank_acc_type_grp_name"
                    + ", case when acc.path = '1' then 'เงินทดรองราชการ' else s.bgm_source_name end as bgm_source_name"
                    + ", bb.bank_seq, b.bank_id, b.bank_name, bb.branch_id, bb.branch_name"
                    + ", bacc.bank_acc_no as saving_bank_acc_no, bacc.bank_acc_name as saving_bank_acc_name"
                    + ", area.work_area_name, gl.account_name, coalesce(acc.path, '0') as path"
                    + ", acc.record_status"
                    + ", acc.created_by, acc.created_when, acc.updated_by, acc.updated_when"
                    + " FROM fim_mas_bank_accounts acc"
                    + " LEFT JOIN fim_mas_bank_account_type_groups tg ON acc.bank_acc_type_grp_seq = tg.bank_acc_type_grp_seq"
                    + " LEFT JOIN fim_mas_bank_branchs bb ON acc.branch_seq = bb.branch_seq"
                    + " LEFT JOIN fim_mas_banks b ON bb.bank_seq = b.bank_seq"
                    + " LEFT JOIN bgm_mas_source s ON acc.bgm_source_seq = s.bgm_source_seq"
                    //+ " LEFT JOIN fim_mas_sub_sources sub ON acc.sub_source_seq = sub.sub_source_seq"
                    + " LEFT JOIN fim_mas_bank_accounts bacc ON acc.saving_bank_acc_seq = bacc.bank_acc_seq"
                    + " LEFT JOIN hrm_mp_work_areas area ON acc.work_area_seq = area.work_area_seq"
                    + " LEFT JOIN acm_mas_account gl ON acc.account_seq = gl.account_seq"
                    + " WHERE acc.bank_acc_seq = @seq"
                    + " ORDER BY acc.bank_acc_no";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.bank_acc_seq = Convert.ToInt32(drc[0]["bank_acc_seq"]);
                    info.bank_acc_no = drc[0]["bank_acc_no"].ToString();
                    info.bank_acc_name = drc[0]["bank_acc_name"].ToString();
                    info.bank_acc_name_eng = drc[0]["bank_acc_name_eng"].ToString();
                    info.bank_acc_type_grp_seq = cn.ConvertNullInt(drc[0]["bank_acc_type_grp_seq"]);
                    info.branch_seq = Convert.ToInt32(drc[0]["branch_seq"]);
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    //info.sub_source_seq = cn.ConvertNullInt(drc[0]["sub_source_seq"]);
                    info.saving_bank_acc_seq = cn.ConvertNullInt(drc[0]["saving_bank_acc_seq"]);
                    info.account_seq = cn.ConvertNullInt(drc[0]["account_seq"]);
                    info.work_area_seq = cn.ConvertNullInt(drc[0]["work_area_seq"]);
                    info.remarks = drc[0]["remarks"].ToString();
                    info.bank_acc_type_grp_name = drc[0]["bank_acc_type_grp_name"].ToString();
                    info.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    //info.sub_source_name = drc[0]["sub_source_name"].ToString();
                    info.bank_seq = cn.ConvertNullInt(drc[0]["bank_seq"]);
                    info.bank_id = drc[0]["bank_id"].ToString();
                    info.bank_name = drc[0]["bank_name"].ToString();
                    info.branch_id = drc[0]["branch_id"].ToString();
                    info.branch_name = drc[0]["branch_name"].ToString();
                    info.saving_bank_acc_no = drc[0]["saving_bank_acc_no"].ToString();
                    info.saving_bank_acc_name = drc[0]["saving_bank_acc_name"].ToString();
                    info.work_area_name = drc[0]["work_area_name"].ToString();
                    info.account_name = drc[0]["account_name"].ToString();
                    info.path = drc[0]["path"].ToString();

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public DataTable GetBankAccountHistory(int bank_acc_seq, DateTime? dFromDate, DateTime? dToDate)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@bank_acc_seq", bank_acc_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(3, "@from_date", dFromDate);
                dbManager.AddParameters(4, "@to_date", dToDate);

                strSQL = "select *"
                + " from (select t.transfer_date as tran_date, t.ref_no, t.transfer_name as tran_name"
                + ", t.amount as in_amount"
                + ", 0 as out_amount"
                + ", case t.transfer_type"
                    + " when 1 then 'ฝาก'"
                    + " when 2 then 'ถอน'"
                    + " when 3 then 'โอน'"
                    + " else 'ไม่ระบุ'"
                    + " end as type"
                + " from fim_transfers t"
                + " where t.record_status <> @delete_status"
                + " and coalesce(t.amount, 0) > 0"
                + " and to_bank_acc_seq = @bank_acc_seq"

                + " union all"

                + " select t.transfer_date as tran_date, t.ref_no, t.transfer_name as tran_name"
                + ", 0 as in_amount"
                + ", t.amount as out_amount"
                + ", case t.transfer_type"
                    + " when 1 then 'ฝาก'"
                    + " when 2 then 'ถอน'"
                    + " when 3 then 'โอน'"
                    + " else 'ไม่ระบุ'"
                    + " end as type"
                + " from fim_transfers t"
                + " where t.record_status <> @delete_status"
                + " and coalesce(t.amount, 0) > 0"
                + " and from_bank_acc_seq = @bank_acc_seq"

                + " union ALL"

                + " select t.receive_date as tran_date, t.receipt_no as ref_no, concat('รับโอนเงินเลขที่ใบเสร็จ ', t.receipt_no) as tran_name"
                + ", t.amount as in_amount"
                + ", 0 as out_amount"
                + ", 'โอน' as type"
                + " from fim_receives t"
                + " where t.receipt_no is not null"
                + " and t.bank_acc_seq = @bank_acc_seq"

                + " union ALL"

                + " select t.real_pay_date as tran_date, t.ref_no as ref_no, t.pay_name as tran_name"
                + ", 0 as in_amount"
                + ", t.amount as out_amount"
                + ", 'จ่ายเช็ค' as type"
                + " from fim_payments t"
                + " inner join (select pc.pay_seq, cb.bank_acc_seq"
                    + " from fim_payment_cheques pc"
                    + " inner join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                    + " inner join fim_cheque_books cb on c.cheque_book_seq = cb.cheque_book_seq"
                    + " where pc.record_status <> @delete_status"
                    + " and c.record_status <> @delete_status"
                    + " and c.cheque_status = 3"
                    + ") pc on t.pay_seq = pc.pay_seq"
                + " where t.record_status = @approve_status"
                + " and t.real_pay_date is not null"
                + " and pc.bank_acc_seq = @bank_acc_seq"

                + " union ALL"

                + " select t.real_pay_date as tran_date, t.ref_no as ref_no, t.pay_name as tran_name"
                + ", 0 as in_amount"
                + ", t.amount as out_amount"
                + ", 'โอน' as type"
                + " from fim_payments t"
                + " where t.record_status = @approve_status"
                + " and t.real_pay_date is not null"
                + " and t.method_seq = 3"
                + " and t.from_bank_acc_id = @bank_acc_seq"

                + " union ALL"

                + " select t.pip_date as tran_date, t.pip_ref_no as ref_no, t.pip_name as tran_name"
                + ", 0 as in_amount"
                + ", t.net_amount as out_amount"
                + ", 'PIP' as type"
                + " from fim_payments_payinpack t"
                + " where t.record_status = @approve_status"
                + " and t.pip_date is not null"
                + " and t.bank_acc_seq = @bank_acc_seq"
                + ") t"
                + " where 0=0"
                + (dFromDate != null ? " and tran_date >= @from_date" : "")
                + (dToDate != null ? " and tran_date <= @to_date" : "")
                + " order by tran_date";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable GetBankAccountReport(int bank_acc_seq, DateTime? dFromDate, DateTime? dToDate)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@bank_acc_seq", bank_acc_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(3, "@from_date", dFromDate);
                dbManager.AddParameters(4, "@to_date", dToDate);

                strSQL = "select *"
                + " from (select t.transfer_date as tran_date, t.ref_no, t.transfer_name as tran_name"
                + ", t.amount as in_amount"
                + ", 0 as out_amount"
                + ", case t.transfer_type"
                    + " when 1 then 'ฝาก'"
                    + " when 2 then 'ถอน'"
                    + " when 3 then 'โอน'"
                    + " else 'ไม่ระบุ'"
                    + " end as type"
                + ", concat(e.first_name, ' ', e.last_name) as emp_name "
                + ", null as cheque_no "
                + " from fim_transfers t"
                + " LEFT JOIN hrm_ps_employees e ON t.emp_seq = e.emp_seq "
                + " where t.record_status <> @delete_status"
                + " and coalesce(t.amount, 0) > 0"
                + " and to_bank_acc_seq = @bank_acc_seq"

                + " union all"

                + " select t.transfer_date as tran_date, t.ref_no, t.transfer_name as tran_name"
                + ", 0 as in_amount"
                + ", t.amount as out_amount"
                + ", case t.transfer_type"
                    + " when 1 then 'ฝาก'"
                    + " when 2 then 'ถอน'"
                    + " when 3 then 'โอน'"
                    + " else 'ไม่ระบุ'"
                    + " end as type"
                + ", concat(e.first_name, ' ', e.last_name) as emp_name "
                + ", null as cheque_no "
                + " from fim_transfers t"
                + " LEFT JOIN hrm_ps_employees e ON t.emp_seq = e.emp_seq "
                + " where t.record_status <> @delete_status"
                + " and coalesce(t.amount, 0) > 0"
                + " and from_bank_acc_seq = @bank_acc_seq"

                + " union ALL"

                + " select t.receive_date as tran_date, t.receipt_no as ref_no, concat('รับโอนเงินเลขที่ใบเสร็จ ', t.receipt_no) as tran_name"
                + ", t.amount as in_amount"
                + ", 0 as out_amount"
                + ", 'โอน' as type"
                + ", concat(e.first_name, ' ', e.last_name) as emp_name"
                + ", null as cheque_no "
                + " from fim_receives t"
                + " LEFT JOIN hrm_ps_employees e ON t.emp_seq = e.emp_seq "
                + " where t.receipt_no is not null"
                + " and t.bank_acc_seq = @bank_acc_seq"

                + " union ALL"

                + " select t.real_pay_date as tran_date, t.ref_no as ref_no, t.pay_name as tran_name"
                + ", 0 as in_amount"
                + ", t.amount as out_amount"
                + ", 'จ่ายเช็ค' as type"
                + ", concat(e.first_name, ' ', e.last_name) as emp_name "
                + ", pc.cheque_no "
                + " from fim_payments t"
                + " LEFT JOIN hrm_ps_employees e ON t.emp_seq = e.emp_seq "
                + " inner join (select pc.pay_seq, cb.bank_acc_seq, c.cheque_no"
                    + " from fim_payment_cheques pc"
                    + " inner join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                    + " inner join fim_cheque_books cb on c.cheque_book_seq = cb.cheque_book_seq"
                    + " where pc.record_status <> @delete_status"
                    + " and c.record_status <> @delete_status"
                    + " and c.cheque_status = 3"
                    + ") pc on t.pay_seq = pc.pay_seq"
                + " where t.record_status = @approve_status"
                + " and t.real_pay_date is not null"
                + " and pc.bank_acc_seq = @bank_acc_seq"

                + " union ALL"

                + " select t.real_pay_date as tran_date, t.ref_no as ref_no, t.pay_name as tran_name"
                + ", 0 as in_amount"
                + ", t.amount as out_amount"
                + ", 'โอน' as type"
                + ", concat(e.first_name, ' ', e.last_name) as emp_name "
                + ", null as cheque_no "
                + " from fim_payments t"
                + " LEFT JOIN hrm_ps_employees e ON t.emp_seq = e.emp_seq "
                + " where t.record_status = @approve_status"
                + " and t.real_pay_date is not null"
                + " and t.method_seq = 3"
                + " and t.from_bank_acc_id = @bank_acc_seq"

                + " union ALL"

                + " select t.pip_date as tran_date, t.pip_ref_no as ref_no, t.pip_name as tran_name"
                + ", 0 as in_amount"
                + ", t.net_amount as out_amount"
                + ", 'PIP' as type"
                + ", null as emp_name"
                + ", null as cheque_no "
                + " from fim_payments_payinpack t"
                + " where t.record_status = @approve_status"
                + " and t.pip_date is not null"
                + " and t.bank_acc_seq = @bank_acc_seq"
                + ") t"
                + " where 0=0"
                + (dFromDate != null ? " and tran_date >= @from_date" : "")
                + (dToDate != null ? " and tran_date <= @to_date" : "")
                + " order by tran_date";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public DataTable SumAmountReport(int bank_acc_seq, DateTime? dFromDate, DateTime? dToDate)
        {
            string strSQL;
            DataTable dtb = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@bank_acc_seq", bank_acc_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(3, "@from_date", dFromDate);
                dbManager.AddParameters(4, "@to_date", dToDate);

                strSQL = " SELECT SUM(in_amount) - SUM(out_amount) as remain_amount FROM( "
                +"select *"
                + " from (select t.transfer_date as tran_date, t.ref_no, t.transfer_name as tran_name"
                + ", t.amount as in_amount"
                + ", 0 as out_amount"
                + ", case t.transfer_type"
                    + " when 1 then 'ฝาก'"
                    + " when 2 then 'ถอน'"
                    + " when 3 then 'โอน'"
                    + " else 'ไม่ระบุ'"
                    + " end as type"
                + ", concat(e.first_name, ' ', e.last_name) as emp_name "
                + ", null as cheque_no "
                + " from fim_transfers t"
                + " LEFT JOIN hrm_ps_employees e ON t.emp_seq = e.emp_seq "
                + " where t.record_status <> @delete_status"
                + " and coalesce(t.amount, 0) > 0"
                + " and to_bank_acc_seq = @bank_acc_seq"

                + " union all"

                + " select t.transfer_date as tran_date, t.ref_no, t.transfer_name as tran_name"
                + ", 0 as in_amount"
                + ", t.amount as out_amount"
                + ", case t.transfer_type"
                    + " when 1 then 'ฝาก'"
                    + " when 2 then 'ถอน'"
                    + " when 3 then 'โอน'"
                    + " else 'ไม่ระบุ'"
                    + " end as type"
                + ", concat(e.first_name, ' ', e.last_name) as emp_name "
                + ", null as cheque_no "
                + " from fim_transfers t"
                + " LEFT JOIN hrm_ps_employees e ON t.emp_seq = e.emp_seq "
                + " where t.record_status <> @delete_status"
                + " and coalesce(t.amount, 0) > 0"
                + " and from_bank_acc_seq = @bank_acc_seq"

                + " union ALL"

                + " select t.receive_date as tran_date, t.receipt_no as ref_no, concat('รับโอนเงินเลขที่ใบเสร็จ ', t.receipt_no) as tran_name"
                + ", t.amount as in_amount"
                + ", 0 as out_amount"
                + ", 'โอน' as type"
                + ", concat(e.first_name, ' ', e.last_name) as emp_name"
                + ", null as cheque_no "
                + " from fim_receives t"
                + " LEFT JOIN hrm_ps_employees e ON t.emp_seq = e.emp_seq "
                + " where "
                + " t.receipt_no Is Not NULL " 
                + " and t.bank_acc_seq = @bank_acc_seq"

                + " union ALL"

                + " select t.real_pay_date as tran_date, t.ref_no as ref_no, t.pay_name as tran_name"
                + ", 0 as in_amount"
                + ", t.amount as out_amount"
                + ", 'จ่ายเช็ค' as type"
                + ", concat(e.first_name, ' ', e.last_name) as emp_name "
                + ", pc.cheque_no "
                + " from fim_payments t"
                + " LEFT JOIN hrm_ps_employees e ON t.emp_seq = e.emp_seq "
                + " inner join (select pc.pay_seq, cb.bank_acc_seq, c.cheque_no"
                    + " from fim_payment_cheques pc"
                    + " inner join fim_cheques c on pc.cheque_seq = c.cheque_seq"
                    + " inner join fim_cheque_books cb on c.cheque_book_seq = cb.cheque_book_seq"
                    + " where pc.record_status <> @delete_status"
                    + " and c.record_status <> @delete_status"
                    + " and c.cheque_status = 3"
                    + ") pc on t.pay_seq = pc.pay_seq"
                + " where t.record_status = @approve_status"
                + " and t.real_pay_date < @from_date" 
                + " and pc.bank_acc_seq = @bank_acc_seq"

                + " union ALL"

                + " select t.real_pay_date as tran_date, t.ref_no as ref_no, t.pay_name as tran_name"
                + ", 0 as in_amount"
                + ", t.amount as out_amount"
                + ", 'โอน' as type"
                + ", concat(e.first_name, ' ', e.last_name) as emp_name "
                + ", null as cheque_no "
                + " from fim_payments t"
                + " LEFT JOIN hrm_ps_employees e ON t.emp_seq = e.emp_seq "
                + " where t.record_status = @approve_status"
                + " and t.real_pay_date < @from_date" 
                + " and t.method_seq = 3"
                + " and t.from_bank_acc_id = @bank_acc_seq"

                + " union ALL"

                + " select t.pip_date as tran_date, t.pip_ref_no as ref_no, t.pip_name as tran_name"
                + ", 0 as in_amount"
                + ", t.net_amount as out_amount"
                + ", 'PIP' as type"
                + ", null as emp_name"
                + ", null as cheque_no "
                + " from fim_payments_payinpack t"
                + " where t.record_status = @approve_status"
                + " and t.pip_date < @from_date "
                + " and t.bank_acc_seq = @bank_acc_seq"
                + ") t"
                + " where 0=0"
                + (dFromDate != null ? " and tran_date >= @from_date" : "")
                + (dToDate != null ? " and tran_date <= @to_date" : "")
                + ") t";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }

        public void Insert(M_FIM_MAS_BankAccounts info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(15);

                dbManager.AddParameters(0, "@bank_acc_seq", info.bank_acc_seq);
                dbManager.AddParameters(1, "@bank_acc_no", info.bank_acc_no);
                dbManager.AddParameters(2, "@bank_acc_name", info.bank_acc_name);
                dbManager.AddParameters(3, "@bank_acc_name_eng", info.bank_acc_name_eng);
                dbManager.AddParameters(4, "@bank_acc_type_grp_seq", info.bank_acc_type_grp_seq);
                dbManager.AddParameters(5, "@branch_seq", info.branch_seq);
                dbManager.AddParameters(6, "@bgm_source_seq", info.bgm_source_seq);
                //dbManager.AddParameters(7, "@sub_source_seq", info.sub_source_seq);
                dbManager.AddParameters(7, "@saving_bank_acc_seq", info.saving_bank_acc_seq);
                dbManager.AddParameters(8, "@account_seq", info.account_seq);
                dbManager.AddParameters(9, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(10, "@remarks", info.remarks);
                dbManager.AddParameters(11, "@path", info.path);

                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@created_by", info.created_by);
                dbManager.AddParameters(14, "@created_when", info.created_when);

                strSQL = "INSERT INTO fim_mas_bank_accounts"
                    + " (bank_acc_no, bank_acc_name, bank_acc_name_eng, bank_acc_type_grp_seq"
                    + ", branch_seq, bgm_source_seq"
                    //+ ", sub_source_seq"
                    + ", saving_bank_acc_seq"
                    + ", account_seq, work_area_seq, remarks, path"
                    + ", record_status, created_by, created_when)"
                    + " VALUES(@bank_acc_no"
                    + ", @bank_acc_name"
                    + ", @bank_acc_name_eng"
                    + ", @bank_acc_type_grp_seq"
                    + ", @branch_seq"
                    + ", @bgm_source_seq"
                    //+ ", @sub_source_seq"
                    + ", @saving_bank_acc_seq"
                    + ", @account_seq"
                    + ", @work_area_seq"
                    + ", @remarks"
                    + ", @path"
                    + ", @record_status"
                    + ", @created_by"
                    + ", @created_when"
                    + " )";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_MAS_BankAccounts info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@bank_acc_seq", info.bank_acc_seq);
                dbManager.AddParameters(1, "@bank_acc_no", info.bank_acc_no);
                dbManager.AddParameters(2, "@bank_acc_name", info.bank_acc_name);
                dbManager.AddParameters(3, "@bank_acc_name_eng", info.bank_acc_name_eng);
                dbManager.AddParameters(4, "@bank_acc_type_grp_seq", info.bank_acc_type_grp_seq);
                dbManager.AddParameters(5, "@branch_seq", info.branch_seq);
                dbManager.AddParameters(6, "@bgm_source_seq", info.bgm_source_seq);
                //dbManager.AddParameters(7, "@sub_source_seq", info.sub_source_seq);
                dbManager.AddParameters(7, "@saving_bank_acc_seq", info.saving_bank_acc_seq);
                dbManager.AddParameters(8, "@account_seq", info.account_seq);
                dbManager.AddParameters(9, "@work_area_seq", info.work_area_seq);
                dbManager.AddParameters(10, "@remarks", info.remarks);
                dbManager.AddParameters(11, "@path", info.path);
                dbManager.AddParameters(12, "@updated_by", info.updated_by);
                dbManager.AddParameters(13, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_mas_bank_accounts"
                    + " SET bank_acc_no = @bank_acc_no"
                    + ", bank_acc_name = @bank_acc_name"
                    + ", bank_acc_name_eng = @bank_acc_name_eng"
                    + ", bank_acc_type_grp_seq = @bank_acc_type_grp_seq"
                    + ", branch_seq = @branch_seq"
                    + ", bgm_source_seq = @bgm_source_seq"
                    //+ ", sub_source_seq = @sub_source_seq"
                    + ", saving_bank_acc_seq = @saving_bank_acc_seq"
                    //+ ", account_seq = @account_seq"
                    + ", work_area_seq = @work_area_seq"
                    + ", remarks = @remarks"
                    + ", path = @path"
                    + ", updated_by = @Updated_by"
                    + ", updated_when = @Updated_when"
                    + " WHERE bank_acc_seq = @bank_acc_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_MAS_BankAccounts info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@bank_acc_seq", info.bank_acc_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_mas_bank_accounts"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE bank_acc_seq = @bank_acc_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
